import openpyxl,os,pyodbc
import pandas as pd
import tkinter as tk
from tkinter import filedialog,messagebox
import numpy as np

# 初始化数据库文件路径变量
datafilepath = None
config_file = '运行配置.xlsx'  # 原始 Excel 文件路径
sql_file='SQL结果.xlsx'       # SQL语句运行结果文件
outfile_path="统计结果"        # 统计结果文件夹
leibie_dic = {}             # 职业类型字典
year_list=[]                 # 统计年份
MDL={}                       # MDL数据
df=pd.DataFrame()

def read_config():
    leibie_dic.clear()
    year_list.clear()
    MDL.clear()
    # 配置文件相对于当前脚本的路径
    workbook = openpyxl.load_workbook(config_file)
    table = workbook['职业类型配置']
    nrows = table.max_row  #获取该sheet中的有效行数

    for rowx in range(2,nrows+1):
        cell1=table.cell(rowx,1).value
        cell2=table.cell(rowx,2).value
        leibie_dic[cell1]=[cell1]
        if cell2!=None:
            leibie_list=cell2.split(' ')
            leibie_dic[cell1].extend(leibie_list)

    table = workbook['统计年份']
    nrows = table.max_row  #获取该sheet中的有效行数
    for rowx in range(2,nrows+1):
        year=str(int(table.cell(rowx,1).value))
        year_list.append(year)
        MDL[year]=float(table.cell(rowx,2).value)

# 定义按钮点击事件，选择文件
def on_button_click():
    # 弹出文件选择对话框
    file_path = filedialog.askopenfilename(title="选择数据库文件", filetypes=[("Access Database Files", "*.mdb;*")])

    # 如果用户选择了文件，将文件路径保存到变量并更新Label显示
    if file_path:
        global datafilepath
        datafilepath = file_path
        # 更新Label显示文件路径
        file_label.config(text=f"选择的文件: {file_path}")
    else:
        file_label.config(text="没有选择文件")

def count_all_years():
    # 读取职业类型配置
    read_config()
    if datafilepath == None:
        messagebox.showwarning("警告", "请先选择数据库！")
        return
    for year in year_list:
        count_one_year(year)
    messagebox.showinfo("提示", "代码运行结束！")

def query_access_db():
    if datafilepath == None:
        messagebox.showwarning("警告", "请先选择数据库！")
        return
    # 获取 SQL 输入框的内容
    query = sql_textbox.get("1.0", "end-1c")  # 获取 SQL 文本内容
    if not query.strip():
        messagebox.showwarning("警告", "请输入 SQL 语句！")
        return
    conn = pyodbc.connect(r"DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" + datafilepath + ";Uid=;Pwd=;")
    cursor = conn.cursor()
    if query!='':
        SQL = query
        df = pd.read_sql(SQL, conn)
        if not os.path.exists(outfile_path):
            os.makedirs(outfile_path)
        df.to_excel('./'+outfile_path+'/'+sql_file, index=False)
    cursor.close()
    conn.close()
    messagebox.showinfo("提示", "执行SQL语句结束！")

# 定义运行按钮点击事件，读取数据库
def count_one_year(year):
    conn = pyodbc.connect(r"DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" + datafilepath + ";Uid=;Pwd=;")
    cursor = conn.cursor()
    SQL = "select 单位名称,样品受理编号_外,职业类别,个人剂量计编号,监测起日期_外,监测止日期_外,检测日期_外,Hp10_外,射线及防护类型 from 历史信息表 where left(样品受理编号_外,4)="+year
    df = pd.read_sql(SQL, conn)
    #清洗数据,将Hp10_外未空的行删除
    df['Hp10_外'].replace('', np.nan, inplace=True)
    df= df.dropna(subset=['Hp10_外'])#删除空值
    #标准化职业类型名
    global leibie_dic
    for key in leibie_dic:
        for leibie in leibie_dic[key]:
            df.loc[df["职业类别"] == leibie, "职业类别"] = key
    # 保存
    if not os.path.exists(outfile_path):
        os.makedirs(outfile_path)
    df.to_excel('./'+outfile_path+'/'+year+'基础数据.xlsx', index=False)

    cursor.close()
    conn.close()
    nrows=df.shape[0]
    all_data={}
    for rowx in range(1,nrows):
        data=df.iloc[rowx]  # 返回由该行中所有单元格的数据组成的列表
        if data[1] not in all_data:
            all_data[data[1]]={}
        if data[2] not in all_data[data[1]]:
            all_data[data[1]][data[2]]={}
            all_data[data[1]][data[2]]["委托单位"] = df.iat[rowx,0]
            all_data[data[1]][data[2]]["开始时间"] = df.iat[rowx,4]
            all_data[data[1]][data[2]]["结束时间"] = df.iat[rowx,5]
            all_data[data[1]][data[2]]["检测时间"] = df.iat[rowx,6]
            all_data[data[1]][data[2]]["检测结果"] = []
        all_data[data[1]][data[2]]["检测结果"].append(data[7])

    workbook = openpyxl.Workbook()
    #统计表3
    # 创建一个worksheet
    worksheet = workbook.active
    worksheet.title='附表3 个人剂量检测报告清单'
    qujian=[0,0.04,1.0,5.0,10.0,15,20,30,50,1000000]
    #设置MDL
    qujian[1]=MDL[year]
    title_list=['委托单位','报告编号','佩戴开始日期','佩戴结束日期','检测日期','职业类别','人数','＜MDL','MDL-1.0','1.0-5','5-10','10-15','15-20','20-30','30-50','≥50']
    for i in range(1,len(title_list)+1):
        worksheet.cell(1,i,title_list[i-1])

    now_rowx=2
    for bianhao in all_data:
        for leibei_key in all_data[bianhao]:
            leibie_data=all_data[bianhao][leibei_key]
            # 参数对应 行, 列, 值
            worksheet.cell(now_rowx, 1, leibie_data["委托单位"])
            worksheet.cell(now_rowx, 2, bianhao)
            worksheet.cell(now_rowx, 3, leibie_data["开始时间"])
            worksheet.cell(now_rowx, 4, leibie_data["结束时间"])
            worksheet.cell(now_rowx, 5, leibie_data["检测时间"])
            worksheet.cell(now_rowx, 6, leibei_key)
            data=leibie_data["检测结果"]
            worksheet.cell(now_rowx, 7, len(data))
            cats=pd.cut(data,qujian,right=False)
            num_list=pd.value_counts(cats,sort=False).tolist()
            for i in range(len(num_list)):
                worksheet.cell(now_rowx, i+8, num_list[i])
            now_rowx+=1

    all_data={}

    for rowx in range(1,nrows):
        data=df.iloc[rowx]   # 返回由该行中所有单元格的数据组成的列表
        if data[0] not in all_data:
            all_data[data[0]]={}
        if data[2] not in all_data[data[0]]:
            all_data[data[0]][data[2]]={}
            all_data[data[0]][data[2]]["检测结果"] = {}
        if data[3] not in all_data[data[0]][data[2]]["检测结果"]:
            all_data[data[0]][data[2]]["检测结果"][data[3]]=[]
        all_data[data[0]][data[2]]["检测结果"][data[3]].append(data[7])

    #统计表4
    # 创建一个worksheet
    worksheet = workbook.create_sheet(title='附表4 个人剂量年度统计表')
    title_list=['委托单位','职业类别','年份','＜MDL','MDL-1.0','1.0-5','5-10','10-15','15-20','20-30','30-50','≥50','应检测人员总数','受监测人员总数','年集体有效剂量(人Sv)']
    for i in range(1,len(title_list)+1):
        worksheet.cell(1,i,title_list[i-1])
    now_rowx=2
    for name in all_data:
        for leibei_key in all_data[name]:
            leibie_data=all_data[name][leibei_key]
            # 参数对应 行, 列, 值
            worksheet.cell(now_rowx, 1, name)
            worksheet.cell(now_rowx, 2, leibei_key)
            worksheet.cell(now_rowx, 3, year)
            data=[]

            all_dose=0.0
            for man_key in leibie_data["检测结果"]:
                man_dose=0.0
                for one_dose in leibie_data["检测结果"][man_key]:
                    man_dose +=float(one_dose)
                    all_dose +=float(one_dose)
                data.append(man_dose)
            cats=pd.cut(data,qujian,right=False)
            num_list=pd.value_counts(cats,sort=False).tolist()
            for i in range(len(num_list)):
                worksheet.cell(now_rowx, i+4, num_list[i])

            nums = len(leibie_data["检测结果"])
            worksheet.cell(now_rowx, 13, nums)
            worksheet.cell(now_rowx, 14, nums)
            worksheet.cell(now_rowx, 15, all_dose)
            now_rowx+=1

    # 保存
    if not os.path.exists(outfile_path):
        os.makedirs(outfile_path)
    workbook.save('./'+outfile_path+'/'+year+'统计结果.xlsx')

# 创建主窗口
root = tk.Tk()
root.title("个人剂量统计工具0.10 by徐州CDC刘念")  # 设置窗口标题
root.geometry("800x600")  # 设置窗口大小

# 创建选择文件按钮并绑定点击事件
button_select_file = tk.Button(root, text="选择数据库", command=on_button_click)
button_select_file.pack(pady=20)

# 创建运行按钮并绑定点击事件
button_run1 = tk.Button(root, text="生成导出文件", command=count_all_years)
button_run1.pack(pady=20)

# 创建Label控件用于显示文件路径
file_label = tk.Label(root, text="选择的文件路径将显示在这里", wraplength=700)
file_label.pack(pady=10)

# 添加 SQL 输入框
sql_label = tk.Label(root, text="请输入 SQL 语句：")
sql_label.pack(pady=10)

sql_textbox = tk.Text(root, height=10, width=80)  # 用于输入 SQL 语句的多行文本框
sql_textbox.pack(pady=10)

# 创建运行按钮
run_button = tk.Button(root, text="执行 SQL", command=query_access_db)
run_button.pack(pady=20)

# 启动主循环
root.mainloop()